package com.hunglv.databasehelper;

import com.hunglv.entity.*;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class BusInfoDatabaseHelper extends SQLiteOpenHelper{
	public static String DB_PATH = "/data/data/com.hunglv.transportalert/databases/";
	public static String DB_NAME = "bus";

	private SQLiteDatabase myDatabase;
	private final Context myContext;
	
	// table bus_info
	private static String TABLE_BUS_INFO = "bus_info";
	
	private static String KEY_BUS_ID = "bus_id";
	public static int BUS_ID_COLUMN = 0;
	
	private static String KEY_BUS_NAME = "bus_name";
	public static int BUS_NAME_COLUMN = 1;
	
	private static String KEY_BUS_STOP = "bus_stop";
	public static int BUS_STOP_COLUMN = 2;
	
	// table stop_info
	private static String TABLE_STOP_INFO = "stop_info";
	
	private static String KEY_STOP_ID = "_id";
	protected static int STOP_ID_COLUMN = 0;
	
	private static String KEY_STOP_NAME = "name";
	protected static int STOP_NAME_COLUMN = 1;
	
	private static String KEY_STOP_LATITUDE = "latitude";
	protected static int STOP_LATITUDE_COLUMN = 2;
	
	private static String KEY_STOP_LONGITUDE = "longitude";
	protected static int STOP_LONGITUDE_COLUMN = 3;

	// END 

	public BusInfoDatabaseHelper(Context context) {
		super(context, DB_NAME, null, 1);
		myContext = context;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		
	}
	
	@Override
	public synchronized void close(){
		if(myDatabase != null){
			myDatabase.close();
		}
		super.close();
	}
	
	public void createDatabase() throws IOException{
		boolean dbExist = checkDatabase();
		if(dbExist){
			// do nothing
			Log.v("BusAlarm", "db is exist");
		}else{
			this.getReadableDatabase();
			try{
				copyDatabase();
			}catch(IOException e){
				throw new Error("Error copy database");
			}
		}
	}
	
	public boolean checkDatabase(){
		SQLiteDatabase checkDb = null;
		try{
			String mypath = DB_PATH + DB_NAME;
			checkDb = SQLiteDatabase.openDatabase(mypath, null, SQLiteDatabase.OPEN_READONLY);
		}catch(SQLiteException e){
			//database does't exist yet.
			Log.v("BusAlarm", "db does't exist yet");
		}
		
		if(checkDb != null){
			checkDb.close();
		}
		return checkDb != null ? true:false;
	}
	
	public void copyDatabase() throws IOException{
		try{
			InputStream myInput = myContext.getAssets().open(DB_NAME);
			String outputFileName = DB_PATH + DB_NAME;
			OutputStream myOutput = new FileOutputStream(outputFileName);
			
			byte[] buffer = new byte[1024];
			int length;
			while((length = myInput.read(buffer)) > 0){
				myOutput.write(buffer, 0 , length);
			}
			
			myOutput.flush();
			myOutput.close();
			myInput.close();
		}catch(IOException e){
			Log.v("BusAlarm", "copy db failed");
		}
	}
	
	public void openDatabase() throws SQLException{
		Log.v("BusAlarm", "open db: "+checkDatabase());
		
		String myPath = DB_PATH + DB_NAME;
		myDatabase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE); 
	}
	
	public Cursor getAllBusCursor(){
		return myDatabase.query(TABLE_BUS_INFO,
						new String[]{ KEY_BUS_ID, KEY_BUS_NAME, KEY_BUS_STOP},
						null, null, null, null, null);
	}
	
	public Cursor getAllStopCursor(){
		return myDatabase.query(TABLE_STOP_INFO,
						new String[]{ KEY_STOP_ID, KEY_STOP_NAME, KEY_STOP_LATITUDE, KEY_STOP_LONGITUDE},
						null, null, null, null, null);
	}
	
	public Cursor setBusCursor(String busId) throws SQLException{
		Cursor result = myDatabase.query(true,
										TABLE_BUS_INFO,
										new String[]{KEY_BUS_ID, KEY_BUS_NAME, KEY_BUS_STOP},
										KEY_BUS_ID+"="+busId,
										null, null, null, null, null);
		if(result.getCount() == 0 || !result.moveToFirst()){
			//throw new SQLException("No bus found!");
			return null;
		}
		
		return result;
	}
	
	public Cursor setStopCursor(int stopId) throws SQLException{
		Cursor result = myDatabase.query(true,
										TABLE_STOP_INFO,
										new String[]{KEY_STOP_ID, KEY_STOP_NAME, KEY_STOP_LATITUDE, KEY_STOP_LONGITUDE},
										KEY_STOP_ID+"="+stopId,
										null, null, null, null, null);
		if(result.getCount() == 0 || !result.moveToFirst()){
			//throw new SQLException("No stop found!");
			return null;
		}
		
		return result;
	}
	
	public BusItem getBusItem(String busId){
		Cursor cursor = myDatabase.query(true,
				TABLE_BUS_INFO,
				new String[]{KEY_BUS_ID, KEY_BUS_NAME, KEY_BUS_STOP},
				KEY_BUS_ID+"="+busId,
				null, null, null, null, null);
		if(cursor.getCount() == 0 || !cursor.moveToFirst()){
			//throw new SQLException("No bus found!");
			return null;
		}
		
		String busName = cursor.getString(BUS_NAME_COLUMN);
		String busStop = cursor.getString(BUS_STOP_COLUMN);
		
		return (new BusItem(busId, busName, busStop));
	}
	
	public StopItem getStopItem(int stopId){
		Cursor cursor = myDatabase.query(true,
				TABLE_STOP_INFO,
				new String[]{KEY_STOP_ID, KEY_STOP_NAME, KEY_STOP_LATITUDE, KEY_STOP_LONGITUDE},
				KEY_STOP_ID+"="+stopId,
				null, null, null, null, null);
		if(cursor.getCount() == 0 || !cursor.moveToFirst()){
			//no entry satisfied
			return null;
		}
		
		String name = cursor.getString(STOP_NAME_COLUMN);
		String lon = cursor.getString(STOP_LONGITUDE_COLUMN);
		String lat = cursor.getString(STOP_LATITUDE_COLUMN);
		
		try{
			return (new StopItem(stopId, name, Double.valueOf(lat), Double.valueOf(lon)));
		}catch(NumberFormatException e){
		}
		
		return null;
	}
}
